package cn.sccl.dirms.monitor.controller;

import cn.sccl.common.database.DataBase;
import cn.sccl.common.database.DataRow;
import cn.sccl.common.database.DataSet;
import cn.sccl.common.model.PageRes;
import cn.sccl.common.model.QueryData;
import cn.sccl.common.zabbix.Zabbix;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.*;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 监控主机
 * Created by liupan on 2017/3/6.
 */
@RestController
@RequestMapping(value = "/dirms/monitor/item")
public class ItemController {

    @Autowired
    private DataBase dataBase;

    /**
     * 获取某个主机的监控项数据
     * @param hostid 主机id
     */
    @RequestMapping(value = "/get/{hostid}", method = {RequestMethod.POST,RequestMethod.GET})
    public List<Map<String,Object>> getItemByHostId(@PathVariable String hostid) {
        Zabbix zabbix = Zabbix.getZabbix();
        Map<String,Object> params = new HashMap<String,Object>();
        params.put("output", "extend");
        params.put("hostids", hostid);
        Map<String, Object> result = zabbix.call("item.get", params);
        List<Map<String, Object>> mapList = (List<Map<String, Object>>) result.get("result");
        return this.organizeItem(mapList);
    }

    /**
     * 获取某一个应用集下面的监控项数据
     * @param applicationid 应用集Id
     */
    @RequestMapping(value = "/getByAppId/{applicationid}", method = {RequestMethod.POST,RequestMethod.GET})
    public List<Map<String,Object>> getItemByAppId(@PathVariable String applicationid) {
        Zabbix zabbix = Zabbix.getZabbix();
        Map<String,Object> params = new HashMap<String,Object>();
        params.put("output", "extend");
        params.put("applicationids", applicationid);
        Map<String, Object> result = zabbix.call("item.get", params);
        List<Map<String, Object>> mapList = (List<Map<String, Object>>) result.get("result");
        return this.organizeItem(mapList);
    }

    /**
     * 获取某一个图形下面的监控项数据
     * @param graphid 图形Id
     */
    @RequestMapping(value = "/getByGraphId/{graphid}", method = {RequestMethod.POST,RequestMethod.GET})
    public List<Map<String,Object>> getItemByGraphId(@PathVariable String graphid) {
        Zabbix zabbix = Zabbix.getZabbix();
        Map<String,Object> params = new HashMap<String,Object>();
        params.put("output", "extend");
        params.put("graphids", graphid);
//        String[] selectTriggers = new String[]{"triggerid","expression","priority"};
        params.put("selectTriggers","extend");
//        params.put("selectTriggers",selectTriggers);
        Map<String, Object> result = zabbix.call("item.get", params);
        List<Map<String, Object>> items = (List<Map<String, Object>>) result.get("result");
        if(items != null && !items.isEmpty()) {
            for (Map<String, Object> item : items) {
                List<Map<String, Object>> triggers = (List<Map<String, Object>>) item.get("triggers");
                List<Map<String, Object>> triggerstemp = new ArrayList<Map<String, Object>>();
                if (triggers != null && !triggers.isEmpty()) {
                    for (Map<String, Object> trigger : triggers) {
                        Map<String, Object> params1 = new HashMap<String, Object>();
                        params1.put("triggerids", trigger.get("triggerid"));
                        params1.put("output", "extend");
                        params1.put("selectFunctions", "extend");
                        Map<String, Object> resp = zabbix.call("trigger.get", params1);
                        List<Map<String, Object>> triggers4Item = (List<Map<String, Object>>) resp.get("result");
                        if (triggers4Item != null && triggers4Item.size() == 1) {
                            triggerstemp.add(triggers4Item.get(0));
                        }
                    }
                    item.put("triggers",triggerstemp);
                }
            }
        }
        return this.organizeItem(items);
    }

    /**
     * 根据itemid获取最新数据
     * @param itemid 监控项id
     */
    @RequestMapping(value = "/getItem/{itemid}", method = {RequestMethod.POST,RequestMethod.GET})
    public List<Map<String,Object>> getItem(@PathVariable String itemid) {
        Zabbix zabbix = Zabbix.getZabbix();
        Map<String,Object> params = new HashMap<String,Object>();
        params.put("itemids", itemid);
        Map<String, Object> result = zabbix.call("item.get", params);
        List<Map<String, Object>> mapList = (List<Map<String, Object>>) result.get("result");
        return this.organizeItem(mapList);
    }

    /**
     * 获取交换机接口数据
     * @return
     */
    @RequestMapping(value = "/getInterface", method = {RequestMethod.POST,RequestMethod.GET})
    public Map<String,Object> getInterface() {
        Zabbix zabbix = Zabbix.getZabbix();
        Map<String,Object> params = new HashMap<String,Object>();
        params.put("application", "STATUS");
        Map<String, Object> result = zabbix.call("item.get", params);
        List<Map<String, Object>> mapList = (List<Map<String, Object>>) result.get("result");
        return this.organizeItem2(mapList);
    }

    /**
     * 修改 监控项下 指定属性的值
     * @param itemId 监控项id
     * @param attributeNames   属性名称
     * @param attributeValues  属性值
     * @return
     */
    @RequestMapping(value = "/update/{itemId}", method = {RequestMethod.POST})
    public List<String> update(@PathVariable String itemId, @RequestParam("attributeNames") String attributeNames,
                                                    @RequestParam("attributeValues") String attributeValues){
        Zabbix zabbix = Zabbix.getZabbix();
        Map<String,Object> params = new HashMap<String,Object>();
        params.put("itemid",itemId);
        String[] names = attributeNames.trim().split(",");
        String[] values = attributeValues.split(",");
        for (int i = 0; i < names.length; i++) {
            params.put(names[i], values[i]);
        }
        Map<String, Object> resp = zabbix.call("item.update", params);
        Map<String, Object> result = (Map<String, Object>) resp.get("result");
        List<String> res = (List<String>) result.get("itemids");
        return res;
    }

    private List<Map<String, Object>> organizeItem(List<Map<String, Object>> itemList) {
        for (Map<String, Object> item : itemList) {
            String name = (String) item.get("name");
            String key = (String) item.get("key_");
            String[] keyArr = key.split(",");
            StringBuffer emailPattern = new StringBuffer(".*\\[");
            for (int i = 0; i < keyArr.length-1; ++i) {
                emailPattern.append("(.*),");
            }
            emailPattern.append("(.*)\\]");
            Pattern pattern = Pattern.compile(emailPattern.toString());
            Matcher matcher = pattern.matcher(key);
            Boolean isMatch = matcher.find();
            if (isMatch) {
                name = matcher.replaceAll(name);
                item.put("name", name);
            }
            item.put("id", item.get("itemid"));
            item.put("leaf", true);
            item.put("type", 5); // 监控项类型属性
        }
        return itemList;
    }

    /**
     * 过滤非接口数据
     * @param itemList
     * @return
     */
    private Map<String, Object> organizeItem2(List<Map<String, Object>> itemList) {
        Map<String, Object> newResult = new HashMap<>();
        for (Map<String, Object> item : itemList) {
            String name = (String) item.get("name");
            String key = (String) item.get("key_");
            if (!name.contains("status") || key.contains("VLAN") || key.contains("Null") || key.contains("Mgmt")) {
                continue;
            }
            String[] keyArr = key.split(",");
            StringBuffer emailPattern = new StringBuffer(".*\\[");
            for (int i = 0; i < keyArr.length-1; ++i) {
                emailPattern.append("(.*),");
            }
            emailPattern.append("(.*)\\]");
            Pattern pattern = Pattern.compile(emailPattern.toString());
            Matcher matcher = pattern.matcher(key);
            Boolean isMatch = matcher.find();
            if (isMatch) {
                name = matcher.replaceAll(name);
                item.put("name", name);
            }
            item.put("id", item.get("itemid"));
            item.put("leaf", true);
            item.put("type", 5); // 监控项类型属性
            newResult.put((String) item.get("itemid"), item);
        }
        return newResult;
    }

    /**
     * 获取某个主机的监控项数据
     * @param hostid 主机id
     */
    @RequestMapping(value = "/get/sql/{hostid}", method = {RequestMethod.GET})
    public Object getItemsByHostid(@PathVariable("hostid") String hostid){
        String sql = "SELECT \n" +
                "\ti.itemid,\n" +
                "\ti.hostid,\n" +
                "\ti.`name`,\n" +
                "\ti.delay ,\n" +
                "\ti.delay_flex,\n" +
                "\ti.value_type,\n" +
                "\ti.units \n" +
                "from items i \n" +
                "where i.hostid = ? and i.`status` = '0'";
        List<String> params = new ArrayList<>();
        params.add(hostid);
        DataSet dataSet = dataBase.query(sql,params.toArray());
        List<Map> res = new ArrayList<>();
        for(int i=0;i<dataSet.RowCount;i++){
            Map<String,String> map = new HashMap<>();
            DataRow row = dataSet.getRow(i);
            map.put("itemid",row.getString("itemid"));
            map.put("hostid",row.getString("hostid"));
            map.put("name",row.getString("name"));
            map.put("delay",row.getString("delay"));
            map.put("delayFlex",row.getString("delay_flex"));
            map.put("valueType",row.getString("value_type"));
            map.put("units",row.getString("units"));
            res.add(map);
        }
        return res;
    }


    /**
     * 获取某监控项的告警
     * @param itemid 监控项id
     */
    @RequestMapping(value = "/get/alert/{itemid}/{timefrom}/{timetill}", method = {RequestMethod.GET})
    public Object getAlertsByItemId(@PathVariable("itemid") String itemid,@PathVariable("timefrom") Long timefrom,@PathVariable("timetill") Long timetill,
                                    @RequestParam Boolean isPage,@RequestParam Integer page,@RequestParam Integer pageSize){
        String sql = " select a.alertid,\n" +
                "\t\t\tDATE_FORMAT(FROM_UNIXTIME(a.clock),'%Y-%m-%d %H:%i:%S') clock,\n" +
                "\t\t\ta.`subject` `name`,\n" +
                "\t\t\tcase a.`status` when 0 then '消息未发送' when 1 then '消息已发送' when 2 then '发送失败，稍后重试' else '未知错误' end `status`,\n" +
                "\t\t\tcase when mt.mediatypeid is null then '所有方式' else mt.type end sendWay,\n" +
                "\t\t\ta.error\n" +
                "from (select * from alerts  where alerttype = 0 ) a\n" +
                "INNER JOIN \n" +
                "(select e.* \n" +
                "\t\t\tfrom (select * from `events`  where source=0 and object=0 and value=1 )e \n" +
                "\t\t\t\t\t\tINNER JOIN (select DISTINCT tt.triggerid from (SELECT * from functions  where itemid = ? )f left JOIN `triggers` tt on f.triggerid = tt.triggerid  ) t \n" +
                "\t\t\ton e.objectid = t.triggerid )\tee on a.eventid = ee.eventid\n" +
                "LEFT JOIN (select mediatypeid,case type when 0 then '邮件' when 1 then '脚本' when 2 then '短信' when 3 then 'Jabber' when 100 then 'Ez Texting' else '未知' end  type from media_type where `status` = 0) mt on a.mediatypeid = mt.mediatypeid\n where 1=1 " ;
        List<Object> params = new ArrayList<>();
        params.add(itemid);

        if(timefrom == null || timefrom <= 0){
            timefrom = 0l;
        }
        if(timetill == null || timetill <= 0){
            timetill = 0l;
        }
        if(timefrom > 0){
            sql += " and a.clock >= ? ";
            params.add(timefrom);
        }
        if(timetill > 0){
            sql += " and a.clock <= ? ";
            params.add(timetill);
        }
        sql += "ORDER BY a.clock desc ";
        PageRes res = new PageRes();
        List<Map> resList;
        if(isPage != null && isPage){
            if(page == null){
                page = 1;
            }
            if(pageSize == null){
                pageSize = 10;
            }
            res = dataBase.queryPageListMap(sql,page,pageSize,params.toArray());
        }else{
            resList = dataBase.queryList(sql,params.toArray());
            if(resList == null){
                resList = new ArrayList<>();
            }
            res.setRows(resList);
        }
        return res;
    }

    @RequestMapping(value = "/get/sql/item/{itemid}", method = {RequestMethod.GET})
    public Object getItemById(@PathVariable("itemid") String itemid){
        String sql = "SELECT\n" +
                "\ta.itemid,\n" +
                "\ta.name `name`,\n" +
                "\ta.delay,\n" +
                "\ta.delay_flex delayFlex,\n" +
                "\th.clock lastTime,\n" +
                "\th.VALUE lastValue\n" +
                "from (select * from items where itemid = ?) a \n" +
                "\t\tLEFT JOIN \n" +
                "\t\t(\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\titemid,\n" +
                "\t\t\t\tDATE_FORMAT(FROM_UNIXTIME(clock),'%Y-%m-%d %H:%i:%S') clock,\n" +
                "\t\t\t\tVALUE\n" +
                "\t\t\tFROM history_uint\n" +
                "\t\t\tWHERE itemid = ? \n" +
                "\t\t\tORDER BY clock * 1000000 + ns / 1000 DESC\n" +
                "\t\t\tLIMIT 0, 1\n" +
                "\t\t) h ON a.itemid = h.itemid";
        List<String> params = new ArrayList<>();
        params.add(itemid);
        params.add(itemid);
        DataSet dataSet = dataBase.query(sql,params.toArray());
        Map<String,String> map = new HashMap<>();
        DataRow row = dataSet.getRow(0);
        map.put("itemid",row.getString("itemid"));
        map.put("name",row.getString("name"));
        map.put("delay",row.getString("delay"));
        map.put("delayFlex",row.getString("delayFlex"));
        map.put("lastTime",row.getString("lastTime"));
        map.put("lastValue",row.getString("lastValue"));
        return map;
    }
    /**
     * 获取监控项的历史报告数据
     *
     *
     *   {
     *       data40:[{value:'',time:''},],
     *       today:[{value:'',time:''},],
     *       yesterday:[{value:'',time:''},],
     *       day3:[{value:'',time:''},],
     *       day7:[{value:'',time:''},],
     *       day30:[{value:'',time:''},],
     *       dayCompare:{data:[{value:1,time:''}],avg:[{min:1,max:1,avg:1}]
     *          data_o:[{value:1,time:''}],avg_o:[{min:1,max:1,avg:1}]
     *       },
     *       weekCompare:{data:[{value:1,time:''}],avg:[{min:1,max:1,avg:1}]
     *          data_o:[{value:1,time:''}],avg_o:[{min:1,max:1,avg:1}]
     *       },
     *       monthCompare:{data:[{value:1,time:''}],avg:[{min:1,max:1,avg:1}]
     *          data_o:[{value:1,time:''}],avg_o:[{min:1,max:1,avg:1}]
     *       }
     *   }
     * @param itemid 监控项id
     * @param valueType  监控项的值类型
     * @return
     */
    @RequestMapping(value = "/getItemHistory/{itemId}/{valueType}", method = {RequestMethod.GET,RequestMethod.POST})
    @ResponseBody
    public Object getItemHistory(@PathVariable("itemId") String itemid,@PathVariable("valueType")Integer valueType){
        JSONObject res = new JSONObject();
        String history = "";
        if(valueType != null){
            switch (valueType){
                case 0:
                    history = "history";
                    break;
                case 1:
                    history = "history_str";
                    return null;
                case 2:
                    history = "history_log";
                    return null;
                case 3:
                    history = "history_uint";
                    break;
                case 4:
                    history = "history_text";
                    return null;
            }
        }
        if (!history.equalsIgnoreCase("")) {
            Connection connection = dataBase.getConnection();
            PreparedStatement preparedStatement = null;
            ResultSet rs = null;
            try {
                List<String> params = new ArrayList<>();
                params.add(itemid);
                //最近40天数据
                String sql = "select \n" +
                        "\th.`value`,\n" +
                        "\tCONCAT(date_format(FROM_UNIXTIME(h.clock), '%Y-%m-%d %H:%i:%S')\t,'.', ROUND(h.ns/1000000,0)) time\n" +
                        "\tfrom "+history+" h \n" +
                        "\twhere h.itemid = ?\t\n" +
                        "\torder by h.clock*1000+h.ns/1000000 desc \n" +
                        "\tLIMIT 0,40";
                preparedStatement = dataBase.initPrepareStatement(connection, sql, params.toArray());
                rs = preparedStatement.executeQuery();
                DataSet data = DataSet.FillDataSet(rs);
                rs.close();
                preparedStatement.close();
                JSONArray arr = new JSONArray();
                for (int i = 0; i < data.RowCount; i++) {
                    getJSONObject(data, arr, i);
                }
                res.put("data40", arr);

                //今天的数据
                sql = "select \n" +
                        "\th.`value`,\n" +
                        "\tCONCAT(date_format(FROM_UNIXTIME(h.clock), '%Y-%m-%d %H:%i:%S')\t,'.', ROUND(h.ns/1000000,0)) time\n" +
                        "\tfrom " + history + " h \n" +
                        "\twhere h.itemid = ? \t\n" +
                        "\tand date_format(FROM_UNIXTIME(h.clock),'%Y-%m-%d') = date_format(now(),'%Y-%m-%d')\n" +
                        "order by h.clock*1000000000+h.ns asc ";
                preparedStatement = dataBase.initPrepareStatement(connection, sql, params.toArray());
                rs = preparedStatement.executeQuery();
                data = DataSet.FillDataSet(rs);
                rs.close();
                preparedStatement.close();
                arr = new JSONArray();
                for (int i = 0; i < data.RowCount; i++) {
                    getJSONObject(data, arr, i);
                }
                res.put("today", arr);

                //昨天的数据
                sql = "select \n" +
                        "\th.`value`,\n" +
                        "\tCONCAT(date_format(FROM_UNIXTIME(h.clock), '%Y-%m-%d %H:%i:%S')\t,'.', ROUND(h.ns/1000000,0)) time\n" +
                        "\tfrom " + history + " h \n" +
                        "\twhere h.itemid = ? \t\n" +
                        "\tand date_format(FROM_UNIXTIME(h.clock),'%Y-%m-%d') = date_format(DATE_ADD(now(),INTERVAL -1 DAY),'%Y-%m-%d')\n" +
                        "order by h.clock*1000000000+h.ns asc ";
                preparedStatement = dataBase.initPrepareStatement(connection, sql, params.toArray());
                rs = preparedStatement.executeQuery();
                data = DataSet.FillDataSet(rs);
                rs.close();
                preparedStatement.close();
                arr = new JSONArray();
                for (int i = 0; i < data.RowCount; i++) {
                    getJSONObject(data, arr, i);
                }
                res.put("yesterday", arr);

                //3天的数据
                sql = "select \n" +
                        "\th.`value`,\n" +
                        "\tCONCAT(date_format(FROM_UNIXTIME(h.clock), '%Y-%m-%d %H:%i:%S')\t,'.', ROUND(h.ns/1000000,0)) time\n" +
                        "\tfrom " + history + " h \n" +
                        "\twhere h.itemid = ? \t\n" +
                        "\tand (h.clock BETWEEN UNIX_TIMESTAMP(DATE_ADD(curdate(),INTERVAL -2 DAY)) and UNIX_TIMESTAMP(now()))\n" +
                        "order by h.clock*1000000000+h.ns asc";
                preparedStatement = dataBase.initPrepareStatement(connection, sql, params.toArray());
                rs = preparedStatement.executeQuery();
                data = DataSet.FillDataSet(rs);
                rs.close();
                preparedStatement.close();
                arr = new JSONArray();
                for (int i = 0; i < data.RowCount; i++) {
                    getJSONObject(data, arr, i);
                }
                res.put("day3", arr);

                //7天的数据
                sql = "select \n" +
                        "\th.`value`,\n" +
                        "\tCONCAT(date_format(FROM_UNIXTIME(h.clock), '%Y-%m-%d %H:%i:%S')\t,'.', ROUND(h.ns/1000000,0)) time\n" +
                        "\tfrom " + history + " h \n" +
                        "\twhere h.itemid = ? \t\n" +
                        "\tand (h.clock BETWEEN UNIX_TIMESTAMP(DATE_ADD(curdate(),INTERVAL -6 DAY)) and UNIX_TIMESTAMP(now()))\n" +
                        "order by h.clock*1000000000+h.ns asc";
                preparedStatement = dataBase.initPrepareStatement(connection, sql, params.toArray());
                rs = preparedStatement.executeQuery();
                data = DataSet.FillDataSet(rs);
                rs.close();
                preparedStatement.close();
                arr = new JSONArray();
                for (int i = 0; i < data.RowCount; i++) {
                    getJSONObject(data, arr, i);
                }
                res.put("day7", arr);

                //30天的数据
                sql = "select \n" +
                        "\th.`value`,\n" +
                        "\tCONCAT(date_format(FROM_UNIXTIME(h.clock), '%Y-%m-%d %H:%i:%S')\t,'.', ROUND(h.ns/1000000,0)) time\n" +
                        "\tfrom " + history + "  h \n" +
                        "\twhere h.itemid = ? \t\n" +
                        "\tand (h.clock BETWEEN UNIX_TIMESTAMP(STR_TO_DATE(date_format(DATE_ADD(now(),INTERVAL -29 DAY),'%Y-%m-%d'),'%Y-%m-%d')) and UNIX_TIMESTAMP(now()))\n" +
                        "order by h.clock*1000000000+h.ns asc";
                preparedStatement = dataBase.initPrepareStatement(connection, sql, params.toArray());
                rs = preparedStatement.executeQuery();
                data = DataSet.FillDataSet(rs);
                rs.close();
                preparedStatement.close();
                arr = new JSONArray();
                for (int i = 0; i < data.RowCount; i++) {
                    getJSONObject(data, arr, i);
                }
                res.put("day30", arr);

                //日对比
                getDayCompare(res, history, params,connection,preparedStatement,rs);

                //周对比
                getWeekCompare(res, history, params,connection,preparedStatement,rs);

                //月对比
                getMonthCompare(res, history, params,connection,preparedStatement,rs);
            }catch (Exception e){
                e.printStackTrace();
            }finally {
                dataBase.closeAllOpenObject(rs, preparedStatement, null);
            }

        }
        return res;
    }

    //日对比
    private void getDayCompare(JSONObject res, String history, List<String> params,Connection connection,PreparedStatement preparedStatement,ResultSet rs) throws SQLException {
        String sql;
        DataSet data;
        JSONArray arr;
        JSONObject dayCompare = new JSONObject();
        //日对比 今天的数据
        sql = "select \n" +
                "\tavg(IFNULL(t.value,0)) value,\n" +
                "\tt.H time \n" +
                "from (\n" +
                "select \n" +
                "\th.`value`,\n" +
                "\tdate_format(FROM_UNIXTIME(h.clock),'%Y-%m-%d %H') time,\n" +
                "\tdate_format(FROM_UNIXTIME(h.clock),'%Y-%m-%d') d,\n" +
                "\tdate_format(FROM_UNIXTIME(h.clock),'%H') H\n" +
                "\tfrom "+history+" h \n" +
                "\twhere h.itemid = ? \t\n" +
                "\tand date_format(FROM_UNIXTIME(h.clock),'%Y-%m-%d') = date_format(now(),'%Y-%m-%d')\n" +
                "order by h.clock*1000000000+h.ns asc \n" +
                ") t GROUP BY t.time,t.H ";
        preparedStatement = dataBase.initPrepareStatement(connection, sql, params.toArray());
        rs = preparedStatement.executeQuery();
        data = DataSet.FillDataSet(rs);
        rs.close();
        preparedStatement.close();
        arr = new JSONArray();
        for (int i = 0; i < data.RowCount; i++) {
            getJSONObject(data, arr, i);
        }
        dayCompare.put("data",arr);
        //日对比 今天的平均数据
        sql = "select \n" +
                "\t\tmin(IFNULL(h.value,0)) min,\n" +
                "\t\tmax(IFNULL(h.value,0)) max,\n" +
                "\t\tavg(IFNULL(h.value,0)) avg\n" +
                "\tfrom "+history+" h \n" +
                "\twhere h.itemid = ? \t\n" +
                "\tand date_format(FROM_UNIXTIME(h.clock),'%Y-%m-%d') = date_format(now(),'%Y-%m-%d')\n" +
                "\tGROUP BY h.itemid;";
        preparedStatement = dataBase.initPrepareStatement(connection, sql, params.toArray());
        rs = preparedStatement.executeQuery();
        data = DataSet.FillDataSet(rs);
        rs.close();
        preparedStatement.close();
        arr = new JSONArray();
        for (int i = 0; i < data.RowCount; i++) {
            getJSONObject2(data, arr, i);
        }
        dayCompare.put("avg",arr);
        //日对比  昨天的数据
        sql = "select \n" +
                "\tavg(IFNULL(t.value,0)) value,\n" +
                "\tt.H time \n" +
                "from (\n" +
                "select \n" +
                "\th.`value`,\n" +
                "\tdate_format(FROM_UNIXTIME(h.clock),'%Y-%m-%d %H') time,\n" +
                "\tdate_format(FROM_UNIXTIME(h.clock),'%Y-%m-%d') d,\n" +
                "\tdate_format(FROM_UNIXTIME(h.clock),'%H') H\n" +
                "\tfrom "+history+" h \n" +
                "\twhere h.itemid = ? \t\n" +
                "\tand date_format(FROM_UNIXTIME(h.clock),'%Y-%m-%d') = date_format(DATE_ADD(now(),INTERVAL -1 DAY),'%Y-%m-%d')\n" +
                "order by h.clock*1000000000+h.ns asc \n" +
                ") t GROUP BY t.time,t.H ";
        preparedStatement = dataBase.initPrepareStatement(connection, sql, params.toArray());
        rs = preparedStatement.executeQuery();
        data = DataSet.FillDataSet(rs);
        rs.close();
        preparedStatement.close();
        arr = new JSONArray();
        for (int i = 0; i < data.RowCount; i++) {
            getJSONObject(data, arr, i);
        }
        dayCompare.put("data_o",arr);
        //日对比 昨天的平均数据
        sql = "select \n" +
                "\t\tmin(IFNULL(h.value,0)) min,\n" +
                "\t\tmax(IFNULL(h.value,0)) max,\n" +
                "\t\tavg(IFNULL(h.value,0)) avg,\n" +
                "\t\tcount(h.itemid)\n" +
                "\tfrom "+history+" h \n" +
                "\twhere h.itemid = ? \t\n" +
                "\tand date_format(FROM_UNIXTIME(h.clock),'%Y-%m-%d') = date_format(DATE_ADD(now(),INTERVAL -1 DAY),'%Y-%m-%d')\n" +
                "\tGROUP BY h.itemid";
        preparedStatement = dataBase.initPrepareStatement(connection, sql, params.toArray());
        rs = preparedStatement.executeQuery();
        data = DataSet.FillDataSet(rs);
        rs.close();
        preparedStatement.close();
        arr = new JSONArray();
        for (int i = 0; i < data.RowCount; i++) {
            getJSONObject2(data, arr, i);
        }
        dayCompare.put("avg_o",arr);
        res.put("dayCompare",dayCompare);
    }

    //周对比
    private void getWeekCompare(JSONObject res, String history, List<String> params,Connection connection,PreparedStatement preparedStatement,ResultSet rs) throws SQLException {
        String sql;
        DataSet data;
        JSONArray arr;
        JSONObject weekCompare = new JSONObject();
        //周对比 本周的数据
        sql = "select \n" +
                "\t\tavg(IFNULL(t.value,0)) value,\n" +
                "\t\tt.d,\n" +
                "\t\tcase t.w when 0 then '星期日' \n" +
                "\t\t\t\t\t\t when 1 then '星期一' \n" +
                "\t\t\t\t\t\t when 2 then '星期二' \n" +
                "\t\t\t\t\t\t when 3 then '星期三' \n" +
                "\t\t\t\t\t\t when 4 then '星期四' \n" +
                "\t\t\t\t\t\t when 5 then '星期五' \n" +
                "\t\t\t\t\t\t when 6 then '星期六' \n" +
                "\t\t\t\t\t\telse '' \t\n" +
                "\t\tend  time\n" +
                "\tfrom (\n" +
                "\tselect \n" +
                "\t\th.`value`,\n" +
                "\t\tdate_format(FROM_UNIXTIME(h.clock),'%Y-%m-%d') d,\n" +
                "\t\tdate_format(FROM_UNIXTIME(h.clock),'%w') w\n" +
                "\t\tfrom "+history+" h \n" +
                "\t\twhere h.itemid = ? \t\n" +
                "\t\tand h.clock >= UNIX_TIMESTAMP(DATE_ADD(curdate(),INTERVAL -convert(date_format(curdate(),'%w'),SIGNED) DAY)) \n" +
                "\t\tand h.clock <  UNIX_TIMESTAMP(now())\n" +
                "\torder by h.clock*1000000000+h.ns asc \n" +
                "\t) t GROUP BY t.d,t.w ";
        preparedStatement = dataBase.initPrepareStatement(connection, sql, params.toArray());
        rs = preparedStatement.executeQuery();
        data = DataSet.FillDataSet(rs);
        rs.close();
        preparedStatement.close();
        arr = new JSONArray();
        for (int i = 0; i < data.RowCount; i++) {
            getJSONObject(data, arr, i);
        }
        weekCompare.put("data",arr);
        //周对比 本周的平均数据
        sql = "select \n" +
                "\t\tmin(IFNULL(h.value,0)) min,\n" +
                "\t\tmax(IFNULL(h.value,0)) max,\n" +
                "\t\tavg(IFNULL(h.value,0)) avg,\n" +
                "\t\tcount(h.value) cout\n" +
                "\tfrom "+history+" h \n" +
                "\twhere h.itemid = ?\t\n" +
                "\tand h.clock >= UNIX_TIMESTAMP(DATE_ADD(curdate(),INTERVAL -convert(date_format(curdate(),'%w'),SIGNED) DAY)) \n" +
                "\t\tand h.clock <  UNIX_TIMESTAMP(now())\n" +
                "\tGROUP BY h.itemid";
        preparedStatement = dataBase.initPrepareStatement(connection, sql, params.toArray());
        rs = preparedStatement.executeQuery();
        data = DataSet.FillDataSet(rs);
        rs.close();
        preparedStatement.close();
        arr = new JSONArray();
        for (int i = 0; i < data.RowCount; i++) {
            getJSONObject2(data, arr, i);
        }
        weekCompare.put("avg",arr);
        //周对比  上周的数据
        sql = "select \n" +
                "\t\tavg(IFNULL(t.value,0)) value,\n" +
                "\t\tt.d,\n" +
                "\t\tcase t.w when 0 then '星期日' \n" +
                "\t\t\t\t\t\t when 1 then '星期一' \n" +
                "\t\t\t\t\t\t when 2 then '星期二' \n" +
                "\t\t\t\t\t\t when 3 then '星期三' \n" +
                "\t\t\t\t\t\t when 4 then '星期四' \n" +
                "\t\t\t\t\t\t when 5 then '星期五' \n" +
                "\t\t\t\t\t\t when 6 then '星期六' \n" +
                "\t\t\t\t\t\telse '' \t\n" +
                "\t\tend  time\n" +
                "\tfrom (\n" +
                "\tselect \n" +
                "\t\th.`value`,\n" +
                "\t\tdate_format(FROM_UNIXTIME(h.clock),'%Y-%m-%d') d,\n" +
                "\t\tdate_format(FROM_UNIXTIME(h.clock),'%w') w\n" +
                "\t\tfrom "+history+" h \n" +
                "\t\twhere h.itemid = ? \t\n" +
                "\t\tand h.clock >= UNIX_TIMESTAMP(DATE_ADD(DATE_ADD(curdate(),INTERVAL -convert(date_format(curdate(),'%w'),SIGNED) DAY),INTERVAL -7 DAY))\n" +
                "\t\tand h.clock <  UNIX_TIMESTAMP(DATE_ADD(curdate(),INTERVAL -convert(date_format(curdate(),'%w'),SIGNED) DAY)) \n" +
                "\torder by h.clock*1000000000+h.ns asc \n" +
                "\t) t GROUP BY t.d,t.w ";
        preparedStatement = dataBase.initPrepareStatement(connection, sql, params.toArray());
        rs = preparedStatement.executeQuery();
        data = DataSet.FillDataSet(rs);
        rs.close();
        preparedStatement.close();
        arr = new JSONArray();
        for (int i = 0; i < data.RowCount; i++) {
            getJSONObject(data, arr, i);
        }
        weekCompare.put("data_o",arr);
        //周对比 上周的平均数据
        sql = "select \n" +
                "\t\tmin(IFNULL(h.value,0)) min,\n" +
                "\t\tmax(IFNULL(h.value,0)) max,\n" +
                "\t\tavg(IFNULL(h.value,0)) avg,\n" +
                "\t\tcount(h.value) cout\n" +
                "\tfrom "+history+" h \n" +
                "\twhere h.itemid = ? \n" +
                "\t\tand h.clock >= UNIX_TIMESTAMP(DATE_ADD(DATE_ADD(curdate(),INTERVAL -convert(date_format(curdate(),'%w'),SIGNED) DAY),INTERVAL -7 DAY))\n" +
                "\t\tand h.clock <  UNIX_TIMESTAMP(DATE_ADD(curdate(),INTERVAL -convert(date_format(curdate(),'%w'),SIGNED) DAY))\n" +
                "GROUP BY h.itemid ";
        preparedStatement = dataBase.initPrepareStatement(connection, sql, params.toArray());
        rs = preparedStatement.executeQuery();
        data = DataSet.FillDataSet(rs);
        rs.close();
        preparedStatement.close();
        arr = new JSONArray();
        for (int i = 0; i < data.RowCount; i++) {
            getJSONObject2(data, arr, i);
        }
        weekCompare.put("avg_o",arr);
        res.put("weekCompare",weekCompare);
    }


    //月对比
    private void getMonthCompare(JSONObject res, String history, List<String> params,Connection connection,PreparedStatement preparedStatement,ResultSet rs) throws SQLException {
        String sql;
        DataSet data;
        JSONArray arr;
        JSONObject monthCompare = new JSONObject();
        //月对比 本月的数据
        sql = "select \n" +
                "\t\tavg(IFNULL(t.value,0)) value ,\n" +
                "\t\tt.d ,\n" +
                "\t\tt.day time\n" +
                "\tfrom (\n" +
                "\tselect \n" +
                "\t\th.`value`,\n" +
                "\t\tdate_format(FROM_UNIXTIME(h.clock),'%Y-%m-%d') d,\n" +
                "\t\tdate_format(FROM_UNIXTIME(h.clock),'%d') day\n" +
                "\t\tfrom "+history+" h \n" +
                "\t\twhere h.itemid = ? \t\n" +
                "\t\tand h.clock >= UNIX_TIMESTAMP(date_add(curdate(), interval - day(curdate()) + 1 day))\n" +
                "\t\tand h.clock <  UNIX_TIMESTAMP(now()) \n" +
                "\torder by h.clock*1000000000+h.ns asc \n" +
                "\t) t GROUP BY t.d ,t.day ";
        preparedStatement = dataBase.initPrepareStatement(connection, sql, params.toArray());
        rs = preparedStatement.executeQuery();
        data = DataSet.FillDataSet(rs);
        rs.close();
        preparedStatement.close();
        arr = new JSONArray();
        for (int i = 0; i < data.RowCount; i++) {
            getJSONObject(data, arr, i);
        }
        monthCompare.put("data",arr);
        //月对比 本月的平均数据
        sql = "select \n" +
                "\t\tmin(IFNULL(h.value,0)) min,\n" +
                "\t\tmax(IFNULL(h.value,0)) max,\n" +
                "\t\tavg(IFNULL(h.value,0)) avg,\n" +
                "\t\tcount(h.value) cout\n" +
                "\tfrom "+history+" h \n" +
                "\twhere h.itemid = ? \t\n" +
                "\t\tand h.clock >= UNIX_TIMESTAMP(date_add(curdate(), interval - day(curdate()) + 1 day))\n" +
                "\t\tand h.clock <  UNIX_TIMESTAMP(now()) \n" +
                "GROUP BY h.itemid ";
        preparedStatement = dataBase.initPrepareStatement(connection, sql, params.toArray());
        rs = preparedStatement.executeQuery();
        data = DataSet.FillDataSet(rs);
        rs.close();
        preparedStatement.close();
        arr = new JSONArray();
        for (int i = 0; i < data.RowCount; i++) {
            getJSONObject2(data, arr, i);
        }
        monthCompare.put("avg",arr);
        //月对比  上月的数据
        sql = "select \n" +
                "\t\tavg(IFNULL(t.value,0)) value,\n" +
                "\t\tt.d,\n" +
                "\t\tt.day time\n" +
                "\tfrom (\n" +
                "\tselect \n" +
                "\t\th.`value`,\n" +
                "\t\tdate_format(FROM_UNIXTIME(h.clock),'%Y-%m-%d') d,\n" +
                "\t\tdate_format(FROM_UNIXTIME(h.clock),'%d') day\n" +
                "\t\tfrom "+history+" h \n" +
                "\t\twhere h.itemid = ? \t\n" +
                "\t\tand h.clock >= UNIX_TIMESTAMP(date_add(curdate() - day(curdate()) + 1, interval -1 month))\n" +
                "\t\tand h.clock <  UNIX_TIMESTAMP(date_add(curdate(), interval - day(curdate()) + 1 day)) \n" +
                "\torder by h.clock*1000000000+h.ns asc \n" +
                "\t) t GROUP BY t.d ,t.day";
        preparedStatement = dataBase.initPrepareStatement(connection, sql, params.toArray());
        rs = preparedStatement.executeQuery();
        data = DataSet.FillDataSet(rs);
        rs.close();
        preparedStatement.close();
        arr = new JSONArray();
        for (int i = 0; i < data.RowCount; i++) {
            getJSONObject(data, arr, i);
        }
        monthCompare.put("data_o",arr);
        //月对比 上月的平均数据
        sql = "select \n" +
                "\t\tmin(IFNULL(h.value,0)) min,\n" +
                "\t\tmax(IFNULL(h.value,0)) max,\n" +
                "\t\tavg(IFNULL(h.value,0)) avg,\n" +
                "\t\tcount(h.value) cout\n" +
                "\tfrom "+history+" h \n" +
                "\twhere h.itemid = ? \t\n" +
                "\t\tand h.clock >= UNIX_TIMESTAMP(date_add(curdate() - day(curdate()) + 1, interval -1 month))\n" +
                "\t\tand h.clock <  UNIX_TIMESTAMP(date_add(curdate(), interval - day(curdate()) + 1 day)) \n" +
                "GROUP BY h.itemid";
        preparedStatement = dataBase.initPrepareStatement(connection, sql, params.toArray());
        rs = preparedStatement.executeQuery();
        data = DataSet.FillDataSet(rs);
        rs.close();
        preparedStatement.close();
        arr = new JSONArray();
        for (int i = 0; i < data.RowCount; i++) {
            getJSONObject2(data, arr, i);
        }
        monthCompare.put("avg_o",arr);
        res.put("monthCompare",monthCompare);
    }
    private static void getJSONObject(DataSet data, JSONArray arr, int i) {
        DataRow row = data.getRow(i);
        JSONObject obj = new JSONObject();
        obj.put("value", row.getString("value"));
        obj.put("time", row.getString("time"));
        arr.add(obj);
    }

    private static void getJSONObject2(DataSet data, JSONArray arr, int i) {
        DataRow row = data.getRow(i);
        JSONObject obj = new JSONObject();
        obj.put("min", row.getString("min"));
        obj.put("max", row.getString("max"));
        obj.put("avg", row.getString("avg"));
        arr.add(obj);
    }



}
